package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.IGatewayChannelInfoConfigDao;
import com.sq.partner.manager.mybatis.model.DeptDto;
import com.sq.partner.manager.mybatis.model.DictAddrDto;
import com.sq.partner.manager.mybatis.model.DictDto;
import com.sq.partner.manager.mybatis.model.GatewayChannelInfo;
import com.sq.partner.manager.mybatis.model.GatewayChannelTranstype;
import com.sq.partner.manager.mybatis.model.GatewaySubChannel;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.TreeDto;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;
/**
 * 
 * @author duguangwei
 *
 */
@Repository
public class GatewayChannelInfoConfigDaoImpl extends BaseDaoImpl implements IGatewayChannelInfoConfigDao {
	private static Logger logger = LogUtil.getLog(GatewayChannelInfoConfigDaoImpl.class);
	
	@Override
	public List<GatewayChannelInfo> getValidListByAppid(
			GatewayChannelInfo channelInfo) throws Exception {
		
		channelInfo.setStatus("1");
		return queryList("queryGatewayChannelInfoByMap", channelInfo);
	}
	
	@Override
	public List<GatewayChannelInfo> getListByDept(GatewayChannelInfo gatewayChannelInfo) throws Exception {
		
		return queryList("queryGatewayChannelInfoByDept", gatewayChannelInfo);
	}
	
	@Override
	public Integer getCount(GatewayChannelInfo gatewayChannelInfo)
			throws Exception {
		// TODO Auto-generated method stub
		return (Integer)query("getChannelInfoCount",gatewayChannelInfo);
	}
	@Override
	public List<GatewayChannelInfo> getList(int current, int pageSize,
			GatewayChannelInfo gatewayChannelInfo) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}



	@Override
	public List<GatewayChannelTranstype> getTranstypeBychannelCode(
			GatewayChannelTranstype channelInfo) throws Exception {
		return queryList("getTranstypeBychannelCode", channelInfo);
	}
	@Override
	public List<GatewayChannelTranstype> loadTransNameByApplication(
			GatewayChannelTranstype channelInfo) throws Exception {
		return queryList("getTranstypeByApplication", channelInfo);
	}
	@Override
	public List<GatewayChannelTranstype> loadTransNameByApplication1(
			GatewayChannelTranstype channelInfo) throws Exception {
		return queryList("getTranstypeByApplication1", channelInfo);
	}
	@Override
	public Page queryByCriteria(Integer pageIndex,
			GatewayChannelInfo channelInfo,GatewayUser user) throws Exception{
		
		String deptStr = CoreDeptDaoImpl.transDeptList2deptCodeStr(user.getDepts());

		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		Page channelInfoPage = null;
		try{
			StringBuilder sql = new StringBuilder();
			//modified by gzz@20150513 for del unnecessary be related
			//String sqlbase = "SELECT DISTINCT A.CHANNEL_CODE,A.CHANNEL_NAME,DECODE(A.CLIENT_NO,  '',  '<a href=''javascript:void(0)'' onclick=''openCreateNewClientNoDiv(this)''>生成投保人编码</a>',  A.CLIENT_NO) AS CLIENT_NO, A.DEPT_CODE, C.DEPT_CNAME,A.TRANS_TYPE,A.OPER_TIME   FROM T_GATEWAY_CHANNEL_INFO A  INNER JOIN T_CORE_DEPT C     ON A.DEPT_CODE = C.DEPT_CODE    AND A.STATUS = '1' LEFT JOIN T_GATEWAY_CHANNEL_APP D  ON A.CHANNEL_CODE = D.CHANNEL_CODE   where  A.DEPT_CODE IN  (SELECT DEPT_CODE  FROM T_CORE_DEPT  CONNECT BY PRIOR DEPT_CODE = SNR_DEPT  START WITH DEPT_CODE IN ('"+deptStr+"'))";
			//LFD
			String sqlbase = "SELECT DISTINCT A.CHANNEL_CODE,A.CHANNEL_NAME,A.CHANNEL_SORT,DECODE(A.CLIENT_NO,  '',  '<a href=''javascript:void(0)'' onclick=\"openCreateNewClientNoDiv(this,''create'')\">生成投保人编码</a>',  A.CLIENT_NO) || CASE  WHEN A.CLIENT_NO IS NOT NULL THEN '  <a href=''javascript:void(0)'' onclick=\"openCreateNewClientNoDiv(this,''reCreate'')\">重新生成</a>' ELSE  '' END AS CLIENT_NO, A.DEPT_CODE, C.DEPT_CNAME,A.TRANS_TYPE,A.OPER_TIME,A.IS_USERANDPW_VALIDATE,A.IS_IP_VALIDATE,A.IS_RSA_ENCRYPT,A.IS_ASYNC,A.IS_AFFERENTPOLNO,uniflag,is_third_plan,A.ESTIMATE_AMOUT,A.ESTIMATE_DES , A.TBR_EMAIL , A.CHANNEL_FLAG  FROM T_GATEWAY_CHANNEL_INFO A  INNER JOIN T_CORE_DEPT C     ON A.DEPT_CODE = C.DEPT_CODE    AND A.STATUS = '1' LEFT JOIN T_GATEWAY_CHANNEL_APP D  ON A.CHANNEL_CODE = D.CHANNEL_CODE where 1=1";
			if(!user.isHQFlag()){
				sqlbase= sqlbase+" and A.DEPT_CODE IN  ('"+deptStr+"')";
			}
			sql.append(sqlbase);
			List params = new ArrayList();
			//根据参数拼接Sql
			if(StrUtil.isNotEmpty(channelInfo.getChannelCode())){
				String channelCode = channelInfo.getChannelCode();
				String channelCodeStr = "%"+channelCode+"%";
				sql.append(" and a.channel_code like ? ");
				params.add(channelCodeStr);
			}
			if(StrUtil.isNotEmpty(channelInfo.getChannelName())){
				String channelName = channelInfo.getChannelName();
				String channelNameStr = "%"+channelName+"%";
				sql.append(" and a.channel_name like ? ");
				params.add(channelNameStr);
			}
			sql.append(" order by oper_time desc");
			logger.info("执行sql++++   "+sql);
				conn = this.getConnection();
				
				Long total = 0L;
				pstmt = conn.prepareStatement("select count(*) from ("+sql.toString()+")");
				
				
				for (int i = 0; i < params.size(); i++) {
					pstmt.setObject(i+1, params.get(i));
				}
				
				rs = pstmt.executeQuery();
				if(rs.next()){
					total = rs.getLong(1);
				}
				
				channelInfoPage = new Page(pageIndex,total);
				
				SqlUtil sqlUtil = new SqlUtil();
				
				String sqlPage = sqlUtil.buildPageSqlForOracle(sql.toString(), channelInfoPage).toString();

				pstmt1 = conn.prepareStatement(sqlPage);
				for (int i = 0; i < params.size(); i++) {
					pstmt1.setObject(i+1, params.get(i));
				}
				
				rs = pstmt1.executeQuery();
				
				List transList = DBUtil.transRs2List(rs);
				//System.out.println(transList.get(0));
				channelInfoPage.setData(transList);
		}
		finally{
			this.close(rs);
			this.close(pstmt);
			this.close(pstmt1);
			this.close(conn);
		}
		return channelInfoPage;
	}
	@Override
	public Page<Map<String,Object>> queryByCriteria1(Integer pageIndex,Integer size,GatewayChannelInfo channelInfo,GatewayUser user,String businessChannelType) throws Exception{
		
		String deptStr = CoreDeptDaoImpl.transDeptList2deptCodeStr(user.getDepts());
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		Page<Map<String,Object>> channelInfoPage = null;
		try{
			StringBuilder sql = new StringBuilder();
			//modified by gzz@20150513 for del unnecessary be related
			//String sqlbase = "SELECT DISTINCT A.CHANNEL_CODE,A.CHANNEL_NAME,DECODE(A.CLIENT_NO,  '',  '<a href=''javascript:void(0)'' onclick=''openCreateNewClientNoDiv(this)''>生成投保人编码</a>',  A.CLIENT_NO) AS CLIENT_NO, A.DEPT_CODE, C.DEPT_CNAME,A.TRANS_TYPE,A.OPER_TIME   FROM T_GATEWAY_CHANNEL_INFO A  INNER JOIN T_CORE_DEPT C     ON A.DEPT_CODE = C.DEPT_CODE    AND A.STATUS = '1' LEFT JOIN T_GATEWAY_CHANNEL_APP D  ON A.CHANNEL_CODE = D.CHANNEL_CODE   where  A.DEPT_CODE IN  (SELECT DEPT_CODE  FROM T_CORE_DEPT  CONNECT BY PRIOR DEPT_CODE = SNR_DEPT  START WITH DEPT_CODE IN ('"+deptStr+"'))";
			//LFD
			String sqlbase = "SELECT DISTINCT A.CHANNEL_CODE,A.CHANNEL_NAME,A.DEPT_CODE,B.DEPT_CNAME,A.IS_ASYNC,A.TRANS_TYPE,DECODE(A.TRANS_TYPE,'1','界面出单','2','系统对接','') TRANS_TYPE_NAME,A.OPER_TIME,A.CHANNEL_SORT,D.BUSINESS_CHANNEL_TYPE,E.CODE_NAME,E.CODE_DESC,E.CODE_STATUS FROM T_GATEWAY_CHANNEL_INFO A INNER JOIN T_CORE_DEPT B ON A.DEPT_CODE = B.DEPT_CODE  AND A.STATUS = '1' LEFT JOIN T_GATEWAY_CHANNEL_APP C  ON A.CHANNEL_CODE = C.CHANNEL_CODE LEFT JOIN T_CHANNEL_BUSINESS_REAL D ON A.CHANNEL_CODE = D.CHANNEL_CODE LEFT JOIN T_GATEWAY_DICT E ON E.CODE = D.BUSINESS_CHANNEL_TYPE  AND E.CODE_TYPE = 'channel_classification'  WHERE 1=1";
			if(!user.isHQFlag()){
				sqlbase= sqlbase+" and A.DEPT_CODE IN  ('"+deptStr+"')";
			}
			sql.append(sqlbase);
			List<String> params = new ArrayList<String>();
			//根据参数拼接Sql
			if(StrUtil.isNotEmpty(channelInfo.getChannelCode())){
				String channelCode = channelInfo.getChannelCode();
				String channelCodeStr = "%"+channelCode+"%";
				sql.append(" AND A.CHANNEL_CODE LIKE ? ");
				params.add(channelCodeStr);
			}
			if(StrUtil.isNotEmpty(channelInfo.getChannelName())){
				String channelName = channelInfo.getChannelName();
				String channelNameStr = "%"+channelName+"%";
				sql.append(" AND A.CHANNEL_NAME LIKE ? ");
				params.add(channelNameStr);
			}
			if(StrUtil.isNotEmpty(businessChannelType)){
				if("未配置".equals(businessChannelType)) {
					sql.append(" AND D.BUSINESS_CHANNEL_TYPE IS NULL ");
				}else {
					String businessChannelTypeStr = "%"+businessChannelType+"%";
					sql.append(" AND D.BUSINESS_CHANNEL_TYPE LIKE ? ");
					params.add(businessChannelTypeStr);
				}
			}
			sql.append(" ORDER BY OPER_TIME DESC");
			logger.info("执行sql++++   "+sql);
			conn = this.getConnection();
			
			Long total = 0L;
			pstmt = conn.prepareStatement("select count(*) from ("+sql.toString()+")");
			
			
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(i+1, params.get(i));
			}
			
			rs = pstmt.executeQuery();
			if(rs.next()){
				total = rs.getLong(1);
			}
			if(size!=null && size!=0){
				channelInfoPage = new Page<Map<String,Object>>(pageIndex,total,size);
			}else{
				channelInfoPage = new Page<Map<String,Object>>(pageIndex,total);
			}
			SqlUtil sqlUtil = new SqlUtil();
			
			String sqlPage = sqlUtil.buildPageSqlForOracle(sql.toString(), channelInfoPage).toString();
			
			pstmt1 = conn.prepareStatement(sqlPage);
			for (int i = 0; i < params.size(); i++) {
				pstmt1.setObject(i+1, params.get(i));
			}
			
			rs = pstmt1.executeQuery();
			
			List transList = DBUtil.transRs2List(rs);
			//System.out.println(transList.get(0));
			channelInfoPage.setData(transList);
		}
		finally{
			this.close(rs);
			this.close(pstmt);
			this.close(pstmt1);
			this.close(conn);
		}
		return channelInfoPage;
	}

	@Override
	public void deleteByChannelCode(String channelCode) throws Exception {
		delete("delGatewayChannelInfoById",channelCode);
	}
	
	@Override
	public void updateChannelAppId(GatewayChannelInfo channelInfo) throws Exception {
		update("updateChannelInfo",channelInfo);
	}

	@Override
	public void save(GatewayChannelInfo channelInfo) throws Exception {
		this.add("insertGatewayChannelInfo",channelInfo);
	}
	
	@Override
	public void update(GatewayChannelInfo channelInfo) throws Exception {
		this.update("updateGatewayChannelInfoNotNull", channelInfo);
	}
	
	@Override
	public void updateToURL(GatewayChannelInfo channelInfo) throws Exception {
		this.update("updateGatewayChannelInfoToUrl", channelInfo);
	}

	@Override
	public GatewayChannelInfo queryByChannelInfo(GatewayChannelInfo channelInfo)
			throws Exception {
		GatewayChannelInfo channelInfoFind = (GatewayChannelInfo)this.query("queryGatewayChannelInfoByMap",channelInfo);
		return channelInfoFind;
	}



	@Override
	public List<GatewayChannelInfo> queryListByChannelInfo(
			GatewayChannelInfo channelInfo) throws Exception {

		List<GatewayChannelInfo> channelInfoList = this.queryList("queryGatewayChannelInfoByMap", channelInfo);
		return channelInfoList;
	}



	@Override
	public Page<Map> queryNotInCreditLmtDefChannelInfo(Page<Map> page)
			throws Exception {
		Map<String, Object> paramMap = page.getMap();
		String deptCode = (String)paramMap.get("deptCode");
		String subChannelCode = (String)paramMap.get("subChannelCode");
		String subChannelName = (String)paramMap.get("subChannelName");
		
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			
			if("00".equals(deptCode)){
				sqlbuf.append("SELECT subchan.sub_channel_code,subchan.sub_channel_name,chan.channel_name,subchan.parent_channel_code FROM t_gateway_sub_channel subchan\n" +
								"                 LEFT JOIN t_gateway_channel_info chan ON subchan.fk_channel_code = chan.channel_code\n" + 
								"                 WHERE subchan.status = '1'\n" + 
								"      			  AND subchan.sub_channel_code NOT IN (SELECT def.partner_code FROM t_trad_credit_lmt_def def WHERE def.status = '1')\n");
			}else{
				sqlbuf.append("SELECT subchan.sub_channel_code,subchan.sub_channel_name,chan.channel_name,subchan.parent_channel_code FROM t_gateway_sub_channel subchan\n" +
								"       LEFT JOIN t_gateway_channel_info chan ON subchan.fk_channel_code = chan.channel_code\n" + 
								"       WHERE subchan.status = '1'\n" + 
								"       AND subchan.sub_channel_code NOT IN (SELECT def.partner_code FROM t_trad_credit_lmt_def def WHERE def.status = '1')\n");
			}
			
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			
			if(StrUtil.isNotEmpty(subChannelCode)){
				sqlbuf.append(" and subchan.sub_channel_code like ? ");
				list.add(subChannelCode);
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(subChannelName)){
				sqlbuf.append(" and subchan.sub_channel_name like ? ");
				list.add(subChannelName);
				islike.add(true);
			}
			
			if(!"00".equals(deptCode)){
				sqlbuf.append("      CONNECT BY PRIOR subchan.sub_channel_code = subchan.parent_channel_code\n" + 
								"      START WITH subchan.sub_channel_code IN(\n" + 
								"                          SELECT chan.channel_code FROM t_gateway_channel_info chan\n" + 
								"                               WHERE chan.dept_code = '" + deptCode + "'\n" + 
								"                                              )");
			}
			
			sqlbuf.append(" ORDER BY subchan.parent_channel_code");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List<Map> resultList = DBUtil.transRs2List(rs);
			page.setData(resultList);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public Page<GatewayChannelInfo> getListByDept(Page<GatewayChannelInfo> page)
			throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select l.CHANNEL_NAME as channelName, l.is_third_plan thirdPlan,l.CHANNEL_CODE as channelCode,l.client_no as clientNo,l.trans_type as transType, l.dept_code as deptCode from t_gateway_channel_info  l where 1=1 ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			GatewayChannelInfo gatewayChannelInfo=page.getType();
			
			if(StrUtil.isNotEmpty(gatewayChannelInfo.getChannelName())){
				sqlbuf.append(" and l.CHANNEL_NAME like ?");
				islike.add(true);
				list.add(gatewayChannelInfo.getChannelName());
			}
			if(StrUtil.isNotEmpty(gatewayChannelInfo.getChannelCode())){
				sqlbuf.append(" and l.CHANNEL_CODE like ?");
				islike.add(true);
				list.add(gatewayChannelInfo.getChannelCode());
			}
			
			List<DeptDto> depts=page.getDepts();
			
			String userDept="";
			Map<String, Object> map=page.getMap();
			if(map==null){
				map=new HashMap<String, Object>();
			}
			String flag=(String) map.get("flag");
			sqlbuf.append(" order by l.oper_time desc");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);

			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		
		return page;
	}

	@Override
	public GatewayChannelInfo queryChannelInfoBychannelCode(String channelCode)
			throws Exception {
		return (GatewayChannelInfo) this.query("queryGatewayChannelInfoById",channelCode);
		}
	//查询省市县三级联动方法
	@Override
	public List<DictAddrDto> queryProvinceCityCounty(DictAddrDto dictAddr)
			throws Exception {
		if(dictAddr == null)
		{
			return null;
		}
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<DictAddrDto> addrLs = new ArrayList<DictAddrDto>();
		StringBuffer sqlbuf=new StringBuffer();
		sqlbuf.append("select t.area_code,t.area_name from t_gateway_dict_addr t where t.parent_code ='"+dictAddr.getParentCode()+"' order by t.area_code");
		try{
			conn = this.getConnection();
			String sql = sqlbuf.toString();
			logger.info("执行SQL："+sql);
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				DictAddrDto addr = new DictAddrDto();
				addr.setAreaCode(rs.getString("area_code"));
				addr.setAreaName(rs.getString("area_name"));
	 			addrLs.add(addr);
			}
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
		return addrLs;
	}
	//查询企业类型和行业类型二级联动方法
	@Override
	public List<DictDto> querySubElementList(DictDto dict) throws Exception {
		if(dict == null)
		{
			return null;
		}
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<DictDto> addrLs = new ArrayList<DictDto>();
		StringBuffer sqlbuf=new StringBuffer();
		sqlbuf.append("select code_type, code, code_name from t_gateway_dict where code_type = '"+dict.getCodeType()+"'and parent_code = '"+dict.getParentCode()+"' order by sort_no");
		try{
			conn = this.getConnection();
			String sql = sqlbuf.toString();
			logger.info("执行SQL："+sql);
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				DictDto tem = new DictDto();
				tem.setCode(rs.getString("code"));
				tem.setCodeName(rs.getString("code_name"));
	 			addrLs.add(tem);
			}
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
		return addrLs;
	}
	
	@Override
	public Page<GatewaySubChannel> queryChannelListByDeptForTree(GatewayUser user,Page<GatewaySubChannel> page) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try {
			StringBuilder sql = new StringBuilder();
			String deptStr = CoreDeptDaoImpl.transDeptList2deptCodeStr(user.getDepts());
			sql.append(" SELECT N.SUB_CHANNEL_CODE, N.SUB_CHANNEL_NAME, N.PARENT_CHANNEL_CODE,L.CHANNEL_NAME,NVL2(N.BRANCH, 'Y', 'N') AS IS_HQ_USER_TYPE,N.Fk_Channel_Code channelCode FROM T_GATEWAY_SUB_CHANNEL N INNER JOIN T_GATEWAY_CHANNEL_INFO L ON N.FK_CHANNEL_CODE = L.CHANNEL_CODE");
			String userDept="";
			if(!user.isHQFlag()){
				List<DeptDto>dpts=user.getDepts();
				/*String str=user.getDepartmentStr();*/
				if(dpts!=null){
					String[] depts= deptStr.split(",");
					for (DeptDto dpt : dpts) {
						userDept+=" union select  '"+ dpt.getDeptCode()+ "' from dual";
					}
				}
				sql.append(" and  L.DEPT_CODE IN ( SELECT DEPT_CODE FROM T_CORE_DEPT WHERE DEPT_CODE LIKE '00%' "+userDept+" ) ");
			}
			sql.append(" WHERE N.STATUS = '1' ");
			List<String> params = new ArrayList<String>();
			if (StrUtil.isNotEmpty(page.getType().getSubChannelCode())) {
				String subChannelCodeStr = "%" + page.getType().getSubChannelCode() + "%";
				sql.append(" and  n.sub_channel_code like ? ");
				params.add(subChannelCodeStr);
			}

			if (StrUtil.isNotEmpty(page.getType().getSubChannelName())) {
				String subChannelNameStr = "%" + page.getType().getSubChannelName() + "%";
				sql.append(" and n.sub_channel_name like ? ");
				params.add(subChannelNameStr);
			}
			sql.append(" START WITH N.PARENT_CHANNEL_CODE IS NULL CONNECT BY PRIOR N.SUB_CHANNEL_CODE = N.PARENT_CHANNEL_CODE");
			conn = this.getConnection();
			Long total = 0L;
			pstmt = conn.prepareStatement("select count(*) from ("+ sql.toString() + ")");
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(i + 1, params.get(i));
			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				total = rs.getLong(1);
			}
			page.setTotal(total);
			SqlUtil sqlUtil = new SqlUtil();
			String sqlPage = sqlUtil.buildPageSqlForOracle(sql.toString(),page).toString();
			logger.debug("执行SQL："+sqlPage);
			pstmt1 = conn.prepareStatement(sqlPage);
			for (int i = 0; i < params.size(); i++) {
				pstmt1.setObject(i + 1, params.get(i));
			}
			rs = pstmt1.executeQuery();
			List subAllChannelInfo = DBUtil.transRs2List(rs);
			page.setData(subAllChannelInfo);
		} finally {
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	/*
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<TreeDto> deptTree=null;
		try{
		deptTree = new ArrayList<TreeDto>();
		String deptStr = CoreDeptDaoImpl.transDeptList2deptCodeStr(user.getDepts());
		String sql = "SELECT N.SUB_CHANNEL_CODE, N.SUB_CHANNEL_NAME, N.PARENT_CHANNEL_CODE FROM T_GATEWAY_SUB_CHANNEL N WHERE N.STATUS = '1'  START WITH N.PARENT_CHANNEL_CODE IS NULL CONNECT BY PRIOR N.SUB_CHANNEL_CODE = N.PARENT_CHANNEL_CODE  "
				+ "AND N.FK_CHANNEL_CODE IN (SELECT M.CHANNEL_CODE    FROM T_GATEWAY_CHANNEL_INFO M   WHERE M.STATUS = '1'     AND M.DEPT_CODE IN (SELECT P.DEPT_CODE FROM T_CORE_DEPT P  WHERE LENGTH(P.DEPT_CODE) = 2 CONNECT BY PRIOR P.DEPT_CODE = P.SNR_DEPT START "
				+ "WITH P.DEPT_CODE IN ('"+deptStr+"')))";
		StringBuffer sqlbuf = new StringBuffer();
		sqlbuf.append("SELECT N.SUB_CHANNEL_CODE, N.SUB_CHANNEL_NAME, N.PARENT_CHANNEL_CODE FROM T_GATEWAY_SUB_CHANNEL N INNER JOIN T_GATEWAY_CHANNEL_INFO L ON N.FK_CHANNEL_CODE = L.CHANNEL_CODE ");
		if(!user.isHQFlag())
		{
			sqlbuf.append("and  L.DEPT_CODE IN ('"+ deptStr+ "')");
		}
		sqlbuf.append(" WHERE N.STATUS = '1'  START WITH N.PARENT_CHANNEL_CODE IS NULL CONNECT BY PRIOR N.SUB_CHANNEL_CODE = N.PARENT_CHANNEL_CODE");
		String sql = sqlbuf.toString();
		conn = this.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			while(rs.next()){
				TreeDto tree = new TreeDto();
				tree.setId(rs.getString(1));
				tree.setName(rs.getString(2));
				tree.setpId(rs.getString(3));
				
				deptTree.add(tree);
			}
		}finally{
			this.close(rs);
			this.close(pstmt);
			this.close(conn);
		}
		return deptTree;
	*/}

	@Override
	public Page queryAppInfoByChannelCode(Integer pageIndex, String channelCode)
			throws Exception {
		
 
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		Page appInfoPage = null;
		try{
			StringBuilder sql = new StringBuilder();
			
			String sqlbase = "SELECT T.CHANNEL_CODE,C.APPID,C.APPNAME,T.TRANS_TYPE FROM T_GATEWAY_CHANNEL_APP W  INNER JOIN T_GATEWAY_CHANNEL_INFO T  ON W.CHANNEL_CODE = T.CHANNEL_CODE    AND T.STATUS = '1'  "+
			"INNER JOIN T_GATEWAY_APP C ON W.APP_ID = C.APPID WHERE  w.channel_code = '"+channelCode+"'";
			sql.append(sqlbase);
			sql.append(" order by W.OPER_TIME desc");
			logger.info("执行sql++++   "+sql);
				conn = this.getConnection();
				
				Long total = 0L;
				pstmt = conn.prepareStatement("select count(*) from ("+sql.toString()+")");
				rs = pstmt.executeQuery();
				if(rs.next()){
					total = rs.getLong(1);
				}
				appInfoPage = new Page(pageIndex,total);
				SqlUtil sqlUtil = new SqlUtil();
				String sqlPage = sqlUtil.buildPageSqlForOracle(sql.toString(), appInfoPage).toString();
				pstmt1 = conn.prepareStatement(sqlPage);
				rs = pstmt1.executeQuery();
				List transList = DBUtil.transRs2List(rs);
				appInfoPage.setData(transList);
		}
		finally{
			this.close(rs);
			this.close(pstmt);
			this.close(pstmt1);
			this.close(conn);
		}
		return appInfoPage;
	}

	@Override
	public Page<GatewayChannelInfo> getChannelInfoListByDeptCode(Page<GatewayChannelInfo> page) throws Exception { 

		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			GatewayChannelInfo gatewayChannelInfo=page.getType();
			if("00".equalsIgnoreCase(gatewayChannelInfo.getDeptCode())){
				sqlbuf.append("select l.CHANNEL_NAME as channelName,l.CHANNEL_CODE as channelCode from t_gateway_channel_info  l where 1=1 and l.status ='1' ");
			}else if(2 == gatewayChannelInfo.getDeptCode().length()){
				sqlbuf.append("select l.CHANNEL_NAME as channelName,l.CHANNEL_CODE as channelCode from t_gateway_channel_info  l where 1=1 and l.status ='1' and l.DEPT_CODE IN  ( SELECT DEPT_CODE FROM T_CORE_DEPT M WHERE  DEPT_CODE LIKE  '00%' OR DEPT_CODE=?)");
				islike.add(false);
				list.add(gatewayChannelInfo.getDeptCode());
			}else{
				sqlbuf.append("SELECT L.CHANNEL_CODE AS CHANNELCODE, L.CHANNEL_NAME AS CHANNELNAME"
						+ "  FROM (SELECT T2.CHANNEL_CODE, T2.CHANNEL_NAME FROM T_GATEWAY_CHANNEL_INFO T2"
						+ " INNER JOIN (SELECT T3.PARTNER_CODE  FROM T_NOCAR_PLAN T3 INNER JOIN (SELECT M.DEPT_CODE"
						+ " FROM T_CORE_DEPT M START WITH DEPT_CODE = ?  CONNECT BY NOCYCLE PRIOR M.DEPT_CODE = M.SNR_DEPT) M"
						+ " ON M.DEPT_CODE = T3.DEPT_CODE UNION SELECT T.PARTNER_NO FROM T_TRAD_INSU_PLAN T"
						+ " INNER JOIN (SELECT M.DEPT_CODE FROM T_CORE_DEPT M START WITH DEPT_CODE = ? "
						+ "  CONNECT BY NOCYCLE  PRIOR M.DEPT_CODE = M.SNR_DEPT) M ON M.DEPT_CODE = T.DPT_CDE"
						+ " UNION SELECT T1.PARTNER_NO FROM T_NLIFE_PLAN T1 INNER JOIN (SELECT M.DEPT_CODE"
						+ " FROM T_CORE_DEPT M START WITH DEPT_CODE = ?  CONNECT BY NOCYCLE PRIOR M.DEPT_CODE = M.SNR_DEPT) M"
						+ " ON M.DEPT_CODE = T1.DPT_CDE) TEM_TBL ON T2.CHANNEL_CODE = TEM_TBL.PARTNER_CODE) L WHERE 1 = 1 ");
				islike.add(false);
				list.add(gatewayChannelInfo.getDeptCode());
				islike.add(false);
				list.add(gatewayChannelInfo.getDeptCode());
				islike.add(false);
				list.add(gatewayChannelInfo.getDeptCode());
			}
			
			if(StrUtil.isNotEmpty(gatewayChannelInfo.getChannelName())){
				sqlbuf.append(" and l.CHANNEL_NAME like ?");
				islike.add(true);
				list.add(gatewayChannelInfo.getChannelName());
			}
			if(StrUtil.isNotEmpty(gatewayChannelInfo.getChannelCode())){
				sqlbuf.append(" and l.CHANNEL_CODE like ?");
				islike.add(true);
				list.add(gatewayChannelInfo.getChannelCode());
			}
			sqlbuf.append(" order by l.CHANNEL_CODE desc");
			sql=sqlbuf.toString();
			logger.debug("++++++param:" + gatewayChannelInfo.getDeptCode());
			logger.debug("执行SQL：" + sql);
			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);

			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		
		return page;
	
	}
	
	@Override
	public Page<GatewayChannelInfo> queryChannelInfoPageOfLife(Page<GatewayChannelInfo> page) throws Exception { 

		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			GatewayChannelInfo gatewayChannelInfo=page.getType();
			if("00".equalsIgnoreCase(gatewayChannelInfo.getDeptCode())){
				sqlbuf.append("select l.CHANNEL_NAME as channelName,l.CHANNEL_CODE as channelCode from t_gateway_channel_info  l where 1=1 and l.status ='1' ");
			}else if(2 == gatewayChannelInfo.getDeptCode().length()){
				sqlbuf.append("select l.CHANNEL_NAME as channelName,l.CHANNEL_CODE as channelCode from t_gateway_channel_info  l where 1=1 and l.status ='1' and l.DEPT_CODE IN (SELECT DEPT_CODE FROM T_CORE_DEPT WHERE DEPT_CODE LIKE '00%' OR DEPT_CODE = ? ) ");
				islike.add(false);
				list.add(gatewayChannelInfo.getDeptCode());
			}else{
				sqlbuf.append("SELECT L.CHANNEL_CODE AS CHANNELCODE, L.CHANNEL_NAME AS CHANNELNAME"
						+ " FROM (SELECT T1.CHANNEL_CODE, T1.CHANNEL_NAME  FROM (SELECT T.PARTNER_NO"
						+ "  FROM T_TRAD_INSU_PLAN T INNER JOIN (SELECT M.DEPT_CODE FROM T_CORE_DEPT M"
						+ " START WITH DEPT_CODE =? CONNECT BY NOCYCLE PRIOR M.DEPT_CODE = M.SNR_DEPT) M"
						+ " ON M.DEPT_CODE = T.DPT_CDE UNION SELECT T2.PARTNER_NO FROM T_NLIFE_PLAN T2 "
						+ " INNER JOIN (SELECT M.DEPT_CODE FROM T_CORE_DEPT M  START WITH DEPT_CODE =? "
						+ " CONNECT BY NOCYCLE PRIOR M.DEPT_CODE = M.SNR_DEPT) M ON M.DEPT_CODE = T2.DPT_CDE) TBL_TEM"
						+ " INNER JOIN T_GATEWAY_CHANNEL_INFO T1 ON TBL_TEM.PARTNER_NO = T1.CHANNEL_CODE) L"
						+ " WHERE 1 = 1 ");
				islike.add(false);
				list.add(gatewayChannelInfo.getDeptCode());
				islike.add(false);
				list.add(gatewayChannelInfo.getDeptCode());
			}
			
			if(StrUtil.isNotEmpty(gatewayChannelInfo.getChannelName())){
				sqlbuf.append(" and L.CHANNEL_NAME like ?");
				islike.add(true);
				list.add(gatewayChannelInfo.getChannelName());
			}
			if(StrUtil.isNotEmpty(gatewayChannelInfo.getChannelCode())){
				sqlbuf.append(" and L.CHANNEL_CODE like ?");
				islike.add(true);
				list.add(gatewayChannelInfo.getChannelCode());
			}
			sqlbuf.append(" order by L.CHANNEL_CODE desc");
			sql=sqlbuf.toString();
			logger.debug("++++++param:" + gatewayChannelInfo.getDeptCode());
			logger.debug("执行SQL：" + sql);
			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);

			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		
		return page;
	
	}
	
	@Override
	public Page<GatewayChannelInfo> queryChannelInfoPageOfFacadeNlife(Page<GatewayChannelInfo> page) throws Exception { 

		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			GatewayChannelInfo gatewayChannelInfo=page.getType();
			sqlbuf.append("SELECT DISTINCT L.CHANNEL_CODE AS CHANNELCODE, L.CHANNEL_NAME AS CHANNELNAME"
					+ " FROM T_GATEWAY_CHANNEL_INFO L"
					+ " INNER JOIN T_NLIFE_PLAN N ON L.CHANNEL_CODE=N.PARTNER_NO "
					+ " INNER JOIN T_NLIFE_PROD P ON N.PROD_CODE=P.PROD_CODE AND P.PROD_TYPE='3006'"
					+ " WHERE L.TRANS_TYPE = '1' AND L.STATUS='1' ");	
			if(StrUtil.isNotEmpty(gatewayChannelInfo.getChannelName())){
				sqlbuf.append(" and L.CHANNEL_NAME like ?");
				islike.add(true);
				list.add(gatewayChannelInfo.getChannelName());
			}
			if(StrUtil.isNotEmpty(gatewayChannelInfo.getChannelCode())){
				sqlbuf.append(" and L.CHANNEL_CODE like ?");
				islike.add(true);
				list.add(gatewayChannelInfo.getChannelCode());
			}
			sqlbuf.append(" order by L.CHANNEL_CODE desc");
			sql=sqlbuf.toString();
			logger.debug("++++++param:" + gatewayChannelInfo.getDeptCode());
			logger.debug("执行SQL：" + sql);
			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);

			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	
	}

	public List<Map> queryChannelInfoList(GatewayChannelInfo channelInfo,String flag)throws Exception{
		Connection conn = null;
		
		PreparedStatement pstmt = null;
		
		ResultSet rs = null;
		
		List<Map> channelInfoList = null;
		
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			if("00".equalsIgnoreCase(channelInfo.getDeptCode())){
				sqlbuf.append("select l.CHANNEL_CODE as channelCode from t_gateway_channel_info  l where 1=1 and l.status ='1' ");
			}else if(2 == channelInfo.getDeptCode().length()){
				sqlbuf.append("select l.CHANNEL_CODE as channelCode from t_gateway_channel_info  l where 1=1 and l.status ='1' and l.DEPT_CODE   in  (SELECT DEPT_CODE FROM T_CORE_DEPT WHERE DEPT_CODE LIKE '00%' OR DEPT_CODE = ? ) ");
				islike.add(false);
				list.add(channelInfo.getDeptCode());
			}else{
				if("life".equals(flag)){
					sqlbuf.append("SELECT L.CHANNEL_CODE as channelCode FROM ("
							+ " SELECT DISTINCT T1.CHANNEL_CODE FROM T_TRAD_INSU_PLAN T"
							+ " INNER JOIN t_trad_agent_info  tri ON T.PARTNER_NO = tri.CHANNEL_CODE"
							+ " INNER JOIN (SELECT M.DEPT_CODE FROM T_CORE_DEPT M WHERE LENGTH(M.DEPT_CODE) = 8 START WITH M.DEPT_CODE = ?"
							+ " CONNECT BY NOCYCLE PRIOR M.DEPT_CODE = M.SNR_DEPT) M   ON M.DEPT_CODE = tri.DPT_CDE "
							+ " INNER JOIN T_GATEWAY_CHANNEL_INFO T1  ON T.PARTNER_NO = T1.CHANNEL_CODE"
							+ " UNION "
							+ " SELECT DISTINCT T1.CHANNEL_CODE  FROM T_TRAD_INSU_PLAN T "
							+ " INNER JOIN (SELECT M.DEPT_CODE FROM T_CORE_DEPT M  WHERE LENGTH(M.DEPT_CODE) = 8  START WITH M.DEPT_CODE = ?"
							+ " CONNECT BY NOCYCLE PRIOR M.DEPT_CODE = M.SNR_DEPT) M  ON M.DEPT_CODE = T.DPT_CDE "
							+ " INNER JOIN T_GATEWAY_CHANNEL_INFO T1   ON T.PARTNER_NO = T1.CHANNEL_CODE "
							+ " UNION   SELECT DISTINCT info1.CHANNEL_CODE   FROM T_NLIFE_PLAN plan  "
							+ "         INNER JOIN t_trad_agent_info info  ON plan.PARTNER_NO = info.CHANNEL_CODE "
							+ "  INNER JOIN (SELECT dept.DEPT_CODE  FROM T_CORE_DEPT dept WHERE LENGTH(dept.DEPT_CODE) = 8 START WITH dept.DEPT_CODE = '"+channelInfo.getDeptCode()+"'"
							+ "  CONNECT BY NOCYCLE PRIOR dept.DEPT_CODE = dept.SNR_DEPT) dept"
							+ "   ON dept.DEPT_CODE = info.DPT_CDE  INNER JOIN T_GATEWAY_CHANNEL_INFO info1 ON plan.PARTNER_NO = info1.CHANNEL_CODE"
							+ "  UNION"
							+" SELECT DISTINCT info2.CHANNEL_CODE  FROM T_NLIFE_PLAN plan1  INNER JOIN (SELECT dept1.DEPT_CODE"
							+" FROM T_CORE_DEPT dept1  WHERE LENGTH(dept1.DEPT_CODE) = 8  START WITH dept1.DEPT_CODE = '"+channelInfo.getDeptCode()+"' "
							+"  CONNECT BY NOCYCLE PRIOR dept1.DEPT_CODE = dept1.SNR_DEPT) dept1 ON dept1.DEPT_CODE = plan1.dpt_cde INNER JOIN T_GATEWAY_CHANNEL_INFO info2"
							+"  ON plan1.PARTNER_NO = info2.CHANNEL_CODE"
							+ " ) L WHERE 1 = 1");
					islike.add(false);
					list.add(channelInfo.getDeptCode());	
				}else if("nocar".equals(flag)){
					sqlbuf.append("SELECT L.CHANNEL_CODE as channelCode FROM ("
							+ " SELECT DISTINCT T1.CHANNEL_CODE FROM T_NOCAR_PLAN T"
							+ " INNER JOIN (SELECT M.DEPT_CODE FROM T_CORE_DEPT M WHERE LENGTH(M.DEPT_CODE) = 8 START WITH M.DEPT_CODE = ?"
							+ " CONNECT BY NOCYCLE PRIOR M.DEPT_CODE = M.SNR_DEPT) M  "
							+ " ON M.DEPT_CODE = T.DEPT_CODE"
							+ " INNER JOIN T_GATEWAY_CHANNEL_INFO T1  ON T.PARTNER_CODE = T1.CHANNEL_CODE"
							+ " ) L WHERE 1 = 1");
				}else if("car".equals(flag)){
						sqlbuf.append(" SELECT L.CHANNEL_CODE as channelCode FROM  ( SELECT T1.CHANNEL_CODE  FROM  T_GATEWAY_CHANNEL_INFO T1  WHERE T1.CHANNEL_CODE IN ( SELECT DISTINCT T.PARTNER_NO");
						sqlbuf.append("    FROM t_Gateway_Policy T ");
						sqlbuf.append("     INNER JOIN (SELECT M.DEPT_CODE");
						sqlbuf.append("     FROM T_CORE_DEPT M   WHERE LENGTH(M.DEPT_CODE) = 8");
						sqlbuf.append("     START WITH M.DEPT_CODE = ?    CONNECT BY NOCYCLE PRIOR M.DEPT_CODE = M.SNR_DEPT) M");
						sqlbuf.append("    ON M.DEPT_CODE = T.DPT_CDE   where  T.Policy_Type='4' ) ) L WHERE 1 = 1");
				}else{
					sqlbuf.append("SELECT L.CHANNEL_CODE as channelCode FROM ("
							+ " SELECT DISTINCT T1.CHANNEL_CODE FROM T_TRAD_INSU_PLAN T"
							+ " INNER JOIN (SELECT M.DEPT_CODE FROM T_CORE_DEPT M WHERE LENGTH(M.DEPT_CODE) = 8 START WITH M.DEPT_CODE = ?"
							+ " CONNECT BY NOCYCLE PRIOR M.DEPT_CODE = M.SNR_DEPT) M   "
							+ " ON M.DEPT_CODE = T.DPT_CDE"
							+ " INNER JOIN T_GATEWAY_CHANNEL_INFO T1  ON T.PARTNER_NO = T1.CHANNEL_CODE"
							+ " UNION"
							+ " SELECT DISTINCT T1.CHANNEL_CODE FROM T_NOCAR_PLAN T"
							+ " INNER JOIN (SELECT M.DEPT_CODE FROM T_CORE_DEPT M WHERE LENGTH(M.DEPT_CODE) = 8 START WITH M.DEPT_CODE = ?"
							+ " CONNECT BY NOCYCLE PRIOR M.DEPT_CODE = M.SNR_DEPT) M  "
							+ " ON M.DEPT_CODE = T.DEPT_CODE"
							+ " INNER JOIN T_GATEWAY_CHANNEL_INFO T1  ON T.PARTNER_CODE = T1.CHANNEL_CODE"
							+ " ) L WHERE 1 = 1");
					islike.add(false);
					list.add(channelInfo.getDeptCode());	
				}
				islike.add(false);
				list.add(channelInfo.getDeptCode());
			}
			
			sql=sqlbuf.toString();
			
			pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
			}
			logger.debug("++++++deptCode:" + channelInfo.getDeptCode());
			logger.debug("执行SQL：" + sql);
			
			rs = pstmt.executeQuery();
			channelInfoList = DBUtil.transRs2List(rs);
			
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
		
		return channelInfoList;
	}

	@Override
	public List<TreeDto> queryChannelInfoListsForTree(String deptCode) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<TreeDto> deptTree = new ArrayList<TreeDto>();
		StringBuffer sqlbuf=new StringBuffer();
		 
		sqlbuf.append("SELECT T.CHANNEL_CODE, T.CHANNEL_NAME, T.DEPT_CODE AS SNR_DEPT FROM T_GATEWAY_CHANNEL_INFO T"
				+ " WHERE T.DEPT_CODE = '"+deptCode+"' UNION SELECT T1.DEPT_CODE, T1.DEPT_CNAME, T1.SNR_DEPT"
						+ " FROM T_CORE_DEPT T1 INNER JOIN (SELECT T2.DEPT_CODE  FROM T_GATEWAY_CHANNEL_INFO T2 WHERE T2.DEPT_CODE = '"+deptCode+"') TEM"
						+ " ON TEM.DEPT_CODE = T1.DEPT_CODE");
		try{
			conn = this.getConnection();
			String sql = sqlbuf.toString();
			logger.info("执行SQL："+sql);
			
			pstmt = conn.prepareStatement(sql);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				TreeDto tree = new TreeDto();
				tree.setId(rs.getString(1));
				tree.setName(rs.getString(2));
				tree.setpId(rs.getString(3));
				
				deptTree.add(tree);
			}
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
		
		return deptTree;
	}

	@Override
	public List<GatewayChannelInfo> queryChannelsByChannelCodeStr(
			String ChannelCodeStr) throws Exception {
		return queryList("queryChannelsByChannelCodeStr",ChannelCodeStr);
	}
	
	@Override
	public List<TreeDto> querySubChannelTreeByParentChannelCode(String parentChannelCode) throws Exception{
		return queryList("querySubChannelTreeByParentChannelCode", parentChannelCode);
	}

	@Override
	public  List<Map<String, Object>> impNlifeInfo(Map<String, Object> map)
			throws Exception {
		// TODO Auto-generated method stub
		return this.queryList("impNlifeInfoMap", map);
	}

	@Override
	public Page<GatewayChannelInfo> getListPlanByDept(
			Page<GatewayChannelInfo> page) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select l.CHANNEL_NAME as channelName, l.is_third_plan thirdPlan,l.CHANNEL_CODE as channelCode,l.client_no as clientNo,l.trans_type as transType, l.dept_code as deptCode,CHANNEL_FLAG channelFlag from t_gateway_channel_info  l where 1=1 ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			GatewayChannelInfo gatewayChannelInfo=page.getType();
			
			if(StrUtil.isNotEmpty(gatewayChannelInfo.getChannelName())){
				sqlbuf.append(" and l.CHANNEL_NAME like ?");
				islike.add(true);
				list.add(gatewayChannelInfo.getChannelName());
			}
			if(StrUtil.isNotEmpty(gatewayChannelInfo.getChannelCode())){
				sqlbuf.append(" and l.CHANNEL_CODE like ?");
				islike.add(true);
				list.add(gatewayChannelInfo.getChannelCode());
			}
			
			List<DeptDto> depts=page.getDepts();
			
			sqlbuf.append(" and l.dept_code in (");
			String userDept="";
			Map<String, Object> map=page.getMap();
			if(map==null){
				map=new HashMap<String, Object>();
			}
			String flag=(String) map.get("flag");
			String deept_code = "";
			if(null!=depts){
				StringBuffer deptbuf=new StringBuffer();
				for(int i=0;i<depts.size();i++){
					deptbuf.append("?,");
					deept_code = depts.get(i).getDeptCode().substring(0,2);
					if(!"00".equals(deept_code)){
						list.add(deept_code+"%");
					}else{
						list.add(deept_code);
					}
					islike.add(false);
				}
				String deptstr=deptbuf.toString();
				deptstr=deptstr.substring(0, deptstr.length()-1);
				if(!"00".equals(deept_code)){//分公司:遍历当前分公司出单机构信息
					sqlbuf.append("SELECT dept_code FROM t_core_dept WHERE DEPT_CODE like "+deptstr);
				}else if("00".equals(deept_code)){
					sqlbuf.append("select dept_code from t_core_dept t connect by prior dept_code = snr_dept start with dept_code in ("+deptstr+")");
				}
			}else{
				sqlbuf.append("''");
			}
			sqlbuf.append(" )");
			sqlbuf.append(" order by l.oper_time desc");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);

			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		
		return page;
	}

	@Override
	public String queryIfInternetPlan(String partnerCode) throws Exception {
		String flag ="0";
		List<GatewayChannelInfo> list = this.queryList("queryIfInternetPlan",partnerCode);
		if("1".equals(list.get(0).getChannelFlag())){
			flag = "1";
		}
		return flag;
	}
}
